# !/usr/bin/env python3
# -*- coding: utf-8 -*-

import logging
import os
from typing import Dict, List

import cx_Oracle

"""复制表结构-oracle"""


class OracleStructureMigrator:
    def __init__(self, source_config: Dict[str, str], target_config: Dict[str, str]):
        """
        初始化迁移器

        Args:
            source_config: 源数据库配置
            target_config: 目标数据库配置
        """
        self.source_config = source_config
        self.target_config = target_config
        self.source_conn = None
        self.target_conn = None

        # 设置NLS_LANG环境变量以避免中文乱码
        os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

    def connect_source(self) -> bool:
        """连接源数据库"""
        try:
            self.source_conn = cx_Oracle.connect(
                user=self.source_config['username'],
                password=self.source_config['password'],
                dsn=f"{self.source_config['host']}:{self.source_config['port']}/{self.source_config['service_name']}"
            )
            logging.info("源Oracle数据库连接成功")
            return True
        except Exception as e:
            logging.error(f"源数据库连接失败: {e}")
            return False

    def connect_target(self) -> bool:
        """连接目标数据库"""
        try:
            self.target_conn = cx_Oracle.connect(
                user=self.target_config['username'],
                password=self.target_config['password'],
                dsn=f"{self.target_config['host']}:{self.target_config['port']}/{self.target_config['service_name']}"
            )
            logging.info("目标Oracle数据库连接成功")
            return True
        except Exception as e:
            logging.error(f"目标数据库连接失败: {e}")
            return False

    def get_table_columns(self, table_name: str, owner: str = None) -> List[Dict]:
        """
        获取表的列定义信息

        Args:
            table_name: 表名
            owner: 表所有者，如果为None则使用当前用户
        """
        if not self.source_conn:
            if not self.connect_source():
                return []

        cursor = self.source_conn.cursor()
        try:
            if owner:
                query = """
                    SELECT column_name, data_type, data_length, data_precision, 
                           data_scale, nullable, data_default
                    FROM all_tab_columns 
                    WHERE table_name = UPPER(:table_name) AND owner = UPPER(:owner)
                    ORDER BY column_id
                """
                cursor.execute(query, table_name=table_name, owner=owner)
            else:
                query = """
                    SELECT column_name, data_type, data_length, data_precision, 
                           data_scale, nullable, data_default
                    FROM user_tab_columns 
                    WHERE table_name = UPPER(:table_name)
                    ORDER BY column_id
                """
                cursor.execute(query, table_name=table_name)

            columns = []
            for row in cursor:
                col_info = {
                    'name': row[0],
                    'data_type': row[1],
                    'data_length': row[2],
                    'data_precision': row[3],
                    'data_scale': row[4],
                    'nullable': row[5] == 'Y',
                    'default': row[6]
                }
                columns.append(col_info)

            return columns

        except Exception as e:
            logging.error(f"获取表列信息失败: {e}")
            return []
        finally:
            cursor.close()

    def get_table_constraints(self, table_name: str, owner: str = None) -> List[Dict]:
        """
        获取表的约束信息

        Args:
            table_name: 表名
            owner: 表所有者
        """
        if not self.source_conn:
            if not self.connect_source():
                return []

        cursor = self.source_conn.cursor()
        try:
            if owner:
                query = """
                    SELECT c.constraint_name, c.constraint_type, 
                           cc.column_name, c.search_condition
                    FROM all_constraints c
                    JOIN all_cons_columns cc ON c.constraint_name = cc.constraint_name 
                         AND c.owner = cc.owner
                    WHERE c.table_name = UPPER(:table_name) 
                      AND c.owner = UPPER(:owner)
                      AND c.constraint_type IN ('P', 'U')
                    ORDER BY c.constraint_name, cc.position
                """
                cursor.execute(query, table_name=table_name, owner=owner)
            else:
                query = """
                    SELECT c.constraint_name, c.constraint_type, 
                           cc.column_name, c.search_condition
                    FROM user_constraints c
                    JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
                    WHERE c.table_name = UPPER(:table_name)
                      AND c.constraint_type IN ('P', 'U')
                    ORDER BY c.constraint_name, cc.position
                """
                cursor.execute(query, table_name=table_name)

            constraints = {}
            for row in cursor:
                constraint_name, constraint_type, column_name, search_condition = row

                if constraint_name not in constraints:
                    constraints[constraint_name] = {
                        'name': constraint_name,
                        'type': constraint_type,
                        'columns': [],
                        'condition': search_condition
                    }

                constraints[constraint_name]['columns'].append(column_name)

            return list(constraints.values())

        except Exception as e:
            logging.error(f"获取表约束信息失败: {e}")
            return []
        finally:
            cursor.close()

    def generate_create_table_ddl(self, table_name: str, columns: List[Dict],
                                  constraints: List[Dict], new_table_name: str = None) -> str:
        """
        生成CREATE TABLE DDL语句

        Args:
            table_name: 原表名
            columns: 列定义列表
            constraints: 约束定义列表
            new_table_name: 新表名，如果为None则使用原表名
        """
        target_table_name = new_table_name or table_name

        # 生成列定义部分
        column_definitions = []
        for col in columns:
            col_def = f'{col["name"]} {col["data_type"]}'

            # 处理数据长度和精度
            if col['data_type'] in ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR'):
                if col['data_length']:
                    col_def += f'({col["data_length"]})'
            elif col['data_type'] in ('NUMBER'):
                if col['data_precision'] and col['data_scale']:
                    col_def += f'({col["data_precision"]},{col["data_scale"]})'
                elif col['data_precision']:
                    col_def += f'({col["data_precision"]})'

            # 处理非空约束
            if not col['nullable']:
                col_def += ' NOT NULL'

            # 处理默认值
            if col['default']:
                # col_def += f' DEFAULT {col["default"]}'
                pass

            column_definitions.append(col_def)

        # 生成约束定义部分
        constraint_definitions = []
        for constr in constraints:
            if constr['type'] == 'P':  # 主键约束
                s = ", ".join([f'{col}' for col in constr['columns']])
                # constr_def = f'CONSTRAINT "{constr["name"]}" PRIMARY KEY ({s})'
                constr_def = f' alter table {table_name} add primary key ({s})'
                constraint_definitions.append(constr_def)
            elif constr['type'] == 'U':  # 唯一约束
                s = ", ".join([f'"{col}"' for col in constr['columns']])
                # constr_def = f'CONSTRAINT "{constr["name"]}" UNIQUE ({s})'

                constraint_definitions.append(constr_def)
            elif constr['type'] == 'C':  # 检查约束
                if constr['condition']:
                    # constr_def = f'CONSTRAINT "{constr["name"]}" CHECK ({constr["condition"]})'
                    constraint_definitions.append(constr_def)

        # 组合完整的CREATE TABLE语句(多个sql)
        sqls = []
        ddl_parts = [f'CREATE TABLE {target_table_name} (']
        ddl_parts.append(',\n  '.join(column_definitions))

        # if constraint_definitions:
        #     ddl_parts.append(',\n  '.join(constraint_definitions))

        ddl_parts.append(')')
        sqls.append('\n'.join(ddl_parts))

        sqls.extend(constraint_definitions)

        # return '\n'.join(ddl_parts)
        return sqls

    def table_exists(self, table_name: str, owner: str = None) -> bool:
        """
        检查表是否已存在

        Args:
            table_name: 表名
            owner: 表所有者
        """
        if not self.target_conn:
            if not self.connect_target():
                return False

        cursor = self.target_conn.cursor()
        try:
            if owner:
                query = """
                    SELECT COUNT(*) FROM all_tables 
                    WHERE table_name = UPPER(:table_name) AND owner = UPPER(:owner)
                """
                cursor.execute(query, table_name=table_name, owner=owner)
            else:
                query = """
                    SELECT COUNT(*) FROM user_tables 
                    WHERE table_name = UPPER(:table_name)
                """
                cursor.execute(query, table_name=table_name)

            count = cursor.fetchone()[0]
            return count > 0

        except Exception as e:
            logging.error(f"检查表是否存在失败: {e}")
            return False
        finally:
            cursor.close()

    def copy_table_structure(self, source_table: str, target_table: str = None,
                             source_owner: str = None, drop_if_exists: bool = False) -> bool:
        """
        复制表结构到目标数据库

        Args:
            source_table: 源表名
            target_table: 目标表名，如果为None则使用源表名
        """
        try:
            target_table = target_table or source_table

            # 检查目标表是否已存在
            if self.table_exists(target_table):
                logging.info(f"目标表 {target_table} 已存在")
                if drop_if_exists:
                    logging.info(f"目标表 {target_table} 已存在，执行删除操作")
                    self.drop_table(target_table)
                return True
            else:
                logging.info(f"目标表 {target_table} 不存在，继续创建")

                # 获取源表结构信息
            logging.info(f"开始获取源表 {source_table} 的结构信息")
            columns = self.get_table_columns(source_table, source_owner)
            if not columns:
                logging.error("无法获取源表的列信息")
                return False

            constraints = self.get_table_constraints(source_table, source_owner)

            indexs = self.get_table_index(source_table)

            # 生成DDL语句
            sqls = self.generate_create_table_ddl(source_table, columns,
                                                  constraints, target_table)
            logging.info(f"生成的DDL语句:\n{sqls}")

            # 在目标数据库中执行DDL
            if not self.execute_ddl_in_target(sqls):
                logging.error("在目标数据库中执行DDL失败")
                return False

            logging.info(f"成功复制表结构: {source_table} -> {target_table}")
            return True

        except Exception as e:
            logging.error(f"复制表结构失败: {e}")
            return False

    def execute_ddl_in_target(self, sqls: list) -> bool:
        """在目标数据库中执行DDL语句"""
        if not self.target_conn:
            if not self.connect_target():
                return False

        cursor = self.target_conn.cursor()
        try:
            for sql in sqls:
                logging.info(sql)
                cursor.execute(sql)
            self.target_conn.commit()
            logging.info("DDL语句执行成功")
            return True
        except Exception as e:
            self.target_conn.rollback()
            logging.error(f"DDL执行失败: {e}")
            return False
        finally:
            cursor.close()

    def drop_table(self, table_name: str) -> bool:
        """删除表"""
        if not self.target_conn:
            if not self.connect_target():
                return False

        cursor = self.target_conn.cursor()
        try:
            cursor.execute(f'DROP TABLE {table_name}')
            self.target_conn.commit()
            logging.info(f"表 {table_name} 删除成功")
            return True
        except Exception as e:
            self.target_conn.rollback()
            logging.error(f"删除表失败: {e}")
            return False
        finally:
            cursor.close()

    def close_connections(self):
        """关闭数据库连接"""
        if self.source_conn:
            self.source_conn.close()
        if self.target_conn:
            self.target_conn.close()
        logging.info("数据库连接已关闭")

    def get_table_index(self, source_table):
        sql = "SELECT index_name, table_name, column_name FROM all_ind_columns WHERE table_name = 'ZCN_GD2_FR_MAS_TYPE'"
        pass


def copy_table(table_name):
    # 配置日志
    logging.basicConfig(level=logging.INFO,
                        format='%(asctime)s - %(levelname)s - %(message)s')

    # 数据库配置
    source_config = {
        'username': 'pigpos',
        'password': 'pigposdev',
        'host': 'TESTZQF.cpchina.cn',
        'port': '1521',
        'service_name': 'TESTZQF'
    }

    target_config = {
        'username': 'bdchfarm',
        'password': 'devfarm',
        'host': '10.240.24.191',
        'port': '1521',
        'service_name': 'UATPGF'
    }

    # 创建迁移器实例
    migrator = OracleStructureMigrator(source_config, target_config)

    try:
        # 复制表结构
        success = migrator.copy_table_structure(
            source_table=table_name,
            target_table=table_name,
            drop_if_exists=False
        )

        if success:
            logging.info("表结构复制完成")
        else:
            logging.error("表结构复制失败")

    finally:
        migrator.close_connections()


if __name__ == "__main__":
    table_names = ('ZCN_MED_PLAN_HEADER,ZCN_MED_PLAN_DETAIL,ZCN_MED_PLAN_DETAIL_LOT,ZCN_PROCESS_TASK,ZCN_MED_PLAN_EXPEND \
                   ,ZCN_MED_PLAN_EXPEND_ATTACHMENT,ZCN_MED_PLAN_ATTACHMENT,ZCN_MED_PLAN_DIAGNOSIS,ZCN_MED_PLAN_SYMPTOM \
                   ,ZCN_MED_PLAN_EXPEND_EFFECT,ZCN_MED_PLAN_EXPEND_LINK_DOC,zcn_gd2_fr_mas_type')
    table_name_list = table_names.split(",")
    for table_name in table_name_list:
        table_name = table_name.upper()
        copy_table(table_name)

### 代码功能说明

# 该解决方案提供了完整的Oracle表结构复制功能：

# - ** 数据库连接管理 **：支持同时连接源和目标Oracle数据库，包含完整的连接异常处理机制。

# - ** 元数据提取 **：通过查询Oracle数据字典视图（all_tab_columns、all_constraints等）获取表的完整结构信息，包括列定义、数据类型、约束条件等。

# - ** DDL语句生成 **：根据提取的元数据自动生成标准的CREATE
# TABLE语句，包含主键约束、唯一约束和检查约束。

# - ** 表存在性检查 **：在创建目标表前检查表是否已存在，支持自动删除已存在表选项。

# - ** 批量表复制支持 **：可扩展支持批量复制多个表结构，适用于数据库迁移场景。

# 该方法避免了直接使用数据库工具的限制，提供了更灵活的编程控制方式，特别适合在自动化脚本和应用程序中集成表结构复制功能。
